# -*- coding: utf-8 -*-
# @Time : 2021-04-26 20:35
# @Author : zj
# @File : class_18.py
# @Software: PyCharm

import pymysql
import dbinfo
import configparser


# 从配置文件读取数据库信息
class OperationMysql(object):
    def __init__(self, db):
        config = configparser.ConfigParser()
        config.read(r'./config.ini')
        host = config.get(db, 'db_host')
        port = int(config.get(db, 'db_port'))
        # port = eval(config.ini.get(db, 'db_port'))
        user = config.get(db, 'db_user')
        password = config.get(db, 'db_password')
        charset = config.get(db, 'db_charset')
        database = config.get(db, 'db_database')
        self.db = pymysql.connect(host=host, user=user, password=password, port=port, charset=charset,
                                  database=database)
        print("数据库开始")

    def db_select(self, sql):
        try:
            cs = self.db.cursor()
            count = cs.execute(sql)
            print('查询到%d条数据' % count)
            for i in range(count):
                ret = cs.fetchone()
                print(ret)
        except Exception as e:
            print(e)
        finally:
            self.db.close()


# 二次封装sql操作
class DataBase(object):
    def __init__(self,
                 host=dbinfo.dbinfo['host'],
                 port=dbinfo.dbinfo['port'],
                 user=dbinfo.dbinfo['user'],
                 password=dbinfo.dbinfo['passwd'],
                 database=dbinfo.dbinfo['database'],
                 charset=dbinfo.dbinfo['charset']):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset

    def __get_connection(self):
        if not self.database:
            raise RuntimeError('数据库没有找到')
        try:
            self.conn = pymysql.connect(host=self.host,
                                        port=self.port,
                                        user=self.user,
                                        password=self.password,
                                        database=self.database,
                                        charset=self.charset)
        except Exception as e:
            raise RuntimeError('初始化数据库失败，原因：', e)
        self.cursor = self.conn.cursor()
        return self.cursor

    def execute_sql(self, sql, params=None):
        # 连接数据库并发送语句
        self.__get_connection().execute(sql, params)
        # 事务提交
        self.conn.commit()
        print('提交成功')

    def __close_sql(self):
        try:
            # 如果存在cursor
            if self.cursor:
                self.cursor.close()
        except Exception as e:
            print('关闭游标异常', e)

    def select_sql(self, sql, params=None):
        self.execute_sql(sql, params)
        # 打印数据
        data = self.cursor.fetchone()
        self.__close_sql()
        return data


if __name__ == "__main__":
    """
    db1 = DataBase()
    sql = 'select * from stu_info where name = %s;'
    print(db1.select_sql(sql, '王昭君'))
    """
    db = OperationMysql('db_2')
    sql = 'select * from stu_info;'
    db.db_select(sql)
